import xlwings
import sys
import os
import time

# 初始化
sys.setrecursionlimit(10000)  # 递归上线
app = xlwings.App(visible=False, add_book=False)  # 创建应用
sht = work_book.sheets["考勤"]  # 打开考勤工作簿

date = sht.range(f"a2:r{row_max + 1}").value  # 获取数据列表
date2 = [["姓名", "时长(小时)"]]  # 汇总列表

time_address = '***.xlsx'


# 函数库
# 创建Book
def add_book():
    book = app.books.add()
    return book


# 读取Book
def read_book(book_address):
    book = app.books.open(f"{book_address}")
    return book


# 保存Book
def save_book(work_book, book_name):
    work_book.save(f'{book_name}')


# 创建Sheet
def add_sheet(work_book, sheet_name, after_name):
    sheet = work_book.sheets.add(f"{sheet_name}", after=after_name)
    return sheet


# 读取Sheet
def read_sheet(work_book, sheet_address):
    sheet = work_book.sheets[sheet_address]
    return sheet


# 写入Sheet
def write_sheet(sheet, sheet_date):
    sheet.range("a1").value = sheet_date


# 获取总行
def get_row_max(sheet):
    info = sheet.used_range  # 找最后行
    row_max = info.last_cell.row
    return row_max


# 汇总在岗时间
def sum_times():
    row = 0  # 行数
    times = 0  # 总时长
    while True:
        if os.path.isfile(f'{time_address}') is False:  # 数据表是否缺失
            print(f'时间数据表：{time_address} 缺失')
            time.sleep(5)
            exit()
        name = date[row][0]  # 获取员工姓名
        # 结束判定
        if name is None:
            print(date2)
            add_sheet("汇总", "考勤", date2)
            work_book.save()  # 保存工作文件
            work_book.close()  # 关闭工作文件
            exit()  # 退出程序
        direction = date[row][14]  # 确认进出门方向
        # 确认个人信息
        if name != date[row + 1][0]:
            hours = round(times / 60, 2)  # 分钟换算小时，保留两位小数
            sht.range(f"s{row + 2}").value = [name, hours]  # 写入数据
            date2.append([name, hours])  # 数据添加进汇总列表
            row += 1
            times = 0
        else:
            # 个人时长叠加
            if direction == '出':
                times += date[row][17]
            row += 1


main()